How to Get the First Row per Group

The function row_number() returns the position of a record in an ordered group. To obtain the first record in a group of records, you can use the with clause to create common table expression including row_number() to obtain the position of each row in the group. Later with an outer query you can filter rows by position.

with rows_and_position as 
    ( 
        select emp_id,
        last_name, 
        salary,
        dept_id,
        row_number() over (partition by dept_id order by salary desc) as position
        from employee
    )
select dept_id, last_name, salary
from  rows_and_position
where position = 1

The approach presented here can be used to obtain records in any position in a group.

IN THIS PAGE